const Koa = require('koa');
const koaStaticCache = require('koa-static-cache');
const KoaRouter = require('koa-router');
const koaBody = require('koa-body');
// const mysql = require('mysql2');
const mysql = require('mysql2/promise');

(async function() {
    // UnhandledPromiseRejectionWarning: Error: Access denied for user 'root'@'localhost' (using password: NO)
    // 一般当前用户  root@localhost 没有访问权限：检查一下你的密码
    // 链接数据库
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        database: 'kkb-mall',
        password: 'sxzpapa11'
    });

    const server = new Koa();

    server.use(koaStaticCache({
        prefix: '/public',
        dir: './public',
        gzip: true,
        dynamic: true
    }));

    const router = new KoaRouter();

    router.get('/users', async (ctx, next) => {
        ctx.body = 'users';
    });


    // 添加新商品
    router.post('/item', koaBody(), async (ctx, next) => {
        // console.log(ctx.request.body);
        let {categoryId, name, price, cover, description} = ctx.request.body;

        // ?,?... => SQL prepared
        // query 的第二个参数是一个数组，里面的值会替换对应的 ?
        let [{insertId}] = await connection.query(
            "INSERT INTO `items` (`category_id`, `name`, `price`, `cover`, `description`) VALUES (?,?,?,?,?)",
            [categoryId, name, price, cover, description]
        );

        // console.log(rs);

        ctx.body = `添加成功，ID是：${insertId}`;
    });

    // 获取 items 列表
    router.get('/items', async (ctx, next) => {

        // 查询数据的总条数
        // let rs = await connection.query(
        //     "SELECT COUNT(`id`) FROM `items` WHERE `price`>100000"
        // );
        // console.log(rs[0][0]['COUNT(`id`)']);

        // let [[{'COUNT(`id`)': count}]] = await connection.query(
        //     "SELECT COUNT(`id`) FROM `items` WHERE `price`>100000"
        // );
        // console.log(count);


        let [[{count}]] = await connection.query(
            "SELECT COUNT(`id`) as `count` FROM `items` WHERE `price`>100000"
        );
        console.log(count);


        // let [items] = await connection.query(
        //     "SELECT * FROM `items` WHERE `price`>100000 ORDER BY `price` DESC, `id` DESC LIMIT 2 OFFSET 1"
        //     // 建议不使用 *
        //     // "SELECT `id` FROM `items`"
        // );


        let [items] = await connection.query(
            "SELECT `items`.`id`, `items`.`name` as `name`, `categories`.`name` as `categoryName`  FROM `items` LEFT JOIN `categories` ON `items`.`category_id`=`categories`.`id` WHERE `items`.`price`>100000 ORDER BY `items`.`price` DESC, `items`.`id` DESC LIMIT 2 OFFSET 1"
            // 建议不使用 *
            // "SELECT `id` FROM `items`"
        );

        // console.log(rs)
        // 如果我们给 ctx.body 赋值一个对象，那么 koa 会自动的把该数据转成json格式，且设置头信息 : content-type: application/json
        ctx.body = items;
    });

    // 更新item
    // /:id koa-router 提供的动态 router
    router.patch('/item/:id(\\d+)', koaBody(), async (ctx, next) => {
        let {price} = ctx.request.body;
        let {id} = ctx.params;

        let rs = await connection.query(
            "UPDATE `items` SET `price`=? WHERE `id`=?",
            [price, id]
        );

        console.log(rs)

        ctx.body = `更新成功 ${id}`;
    });

    // 删除
    router.delete('/item/:id', async (ctx, next) => {
        let {id} = ctx.params;

        let rs = await connection.query(
            "DELETE FROM `items` WHERE `id`=?",
            [id]
        );

        ctx.body = `删除成功: ${id}`;
    });


    server.use(router.routes());

    server.listen(8888);
})()
